package com.example.farmrental.repository;

import com.example.farmrental.entity.RentalOrderItem;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.time.LocalDateTime;
import java.util.List;

public interface RentalOrderItemRepository extends JpaRepository<RentalOrderItem, Long> {
    /**
     * 热门农机统计（按明细中的名称统计Top N）
     */
    @Query(value = "SELECT COALESCE(machinery_name,'未命名') AS name, COUNT(*) AS cnt FROM rental_order_item GROUP BY machinery_name ORDER BY cnt DESC LIMIT ?1", nativeQuery = true)
    List<Object[]> topPopular(int limit);

    /**
     * 指定时间段内热门农机 Top N（按订单明细统计）
     */
    @Query(value = "SELECT COALESCE(roi.machinery_name,'未命名') AS name, COUNT(*) AS cnt FROM rental_order_item roi JOIN rental_order ro ON roi.order_id = ro.id WHERE ro.order_time >= ?1 AND ro.order_time <= ?2 GROUP BY roi.machinery_name ORDER BY cnt DESC LIMIT ?3", nativeQuery = true)
    List<Object[]> topPopularInRange(LocalDateTime start, LocalDateTime end, int limit);
}

